const mysql = require("mysql");
const jwt = require("jsonwebtoken");
const dayjs = require("dayjs");
const Decimal = require("decimal.js");
const qiniu = require("qiniu");
const qiniuConfig = require("../config/qiniu");
const multer = require("multer");
const upload = multer({ storage: multer.memoryStorage() });
//链接数据库
const pool = mysql.createConnection({
  host: "8.137.116.148",
  port: "3306",
  user: "management",
  password: "wkjGD32hRAPX6YRK",
  database: "management",
  multipleStatements: true,
});
function serverErr(res) {
  res.status(500).send({ code: "500", message: "服务器错误" });
}
function tokenErr(res) {
  res.status(401).send({ code: "401", message: "身份已过期" });
}
pool.connect();
//每隔20分钟ping一次数据库
setInterval(() => {
  pool.query("SELECT 1");
}, 20 * 60 * 1000);
//登录接口
exports.login = (req, res) => {
  const { accountNumber, passWord } = req.body;
  const sql = `SELECT * FROM user WHERE accountNumber = ? AND passWord = ?`;
  pool.query(sql, [accountNumber, passWord], (err, result) => {
    if (err) {
      return serverErr(res);
    } else if (result.length === 0) {
      res.status(200).send({ code: "400", message: "用户名或密码错误" });
    } else {
      const payload = {
        accountNumber: accountNumber,
        permission: result[0].permission,
        id: result[0].id,
        name: result[0].name,
        passWord: result[0].passWord,
        amount: result[0].amount,
      };
      const userInfo = {
        accountNumber: result[0].accountNumber,
        permission: result[0].permission,
        id: result[0].id,
        name: result[0].name,
        amount: result[0].amount,
      };
      const token = jwt.sign(payload, "secret", { expiresIn: "1h" });
      res
        .status(200)
        .send({ code: "200", message: "登录成功", token: token, userInfo });
    }
  });
};
//查询当前个人信息
exports.userInfoApi = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      const sql = `SELECT * FROM user WHERE id = ?`;
      pool.query(sql, [decoded.id], (err, result) => {
        if (err) {
          return serverErr(res);
        } else {
          res
            .status(200)
            .send({ code: "200", message: "查询成功", userInfo: result });
        }
      });
    });
  }
};
//判断是否有token
function verifyToken(token) {
  return new Promise((resolve, reject) => {
    jwt.verify(token, "secret", (err, decoded) => {
      if (err) {
        if (err.name === "TokenExpiredError") {
          const payload = jwt.decode(token);
          const newToken = jwt.sign(payload, "secret", { expiresIn: "1h" });
          resolve({ decoded, newToken });
        } else {
          reject(err);
        }
      } else {
        resolve(decoded);
      }
    });
  });
}
function getNowData() {
  return dayjs().format("YYYY-MM-DD HH:mm:ss");
}

// 七牛云上传图片接口
exports.uploadImage = [
  upload.single("file"),
  (req, res) => {
    const file = req.file;
    const fileName = req.body.fileName || file.originalname;
    const mac = new qiniu.auth.digest.Mac(
      qiniuConfig.accessKey,
      qiniuConfig.secretKey
    );
    const options = {
      scope: qiniuConfig.bucket,
      expires: 7200,
    };
    const putPolicy = new qiniu.rs.PutPolicy(options);
    const uploadToken = putPolicy.uploadToken(mac);
    // 配置上传
    const config = new qiniu.conf.Config();
    config.zone = qiniu.zone.Zone_z2; // 华南区
    const formUploader = new qiniu.form_up.FormUploader(config);
    const putExtra = new qiniu.form_up.PutExtra();
    // 设置文件路径，添加commodity目录
    const key = `commodity/${fileName}`;
    // 直接使用buffer上传
    formUploader.put(
      uploadToken,
      key,
      file.buffer, // 使用文件buffer
      putExtra,
      (err, body, info) => {
        if (err) {
          return serverErr(res);
        }
        if (info.statusCode === 200) {
          const fileUrl = `${qiniuConfig.domain}${body.key}`;
          res.status(200).send({
            code: "200",
            message: "上传成功",
            data: { url: fileUrl },
          });
        } else {
          res.status(200).send({ code: "400", message: "上传失败" });
        }
      }
    );
  },
];
// 七牛云删除图片接口
exports.deleteImage = (req, res) => {
  const { url } = req.body;
  const mac = new qiniu.auth.digest.Mac(
    qiniuConfig.accessKey,
    qiniuConfig.secretKey
  );
  const config = new qiniu.conf.Config();
  config.zone = qiniu.zone.Zone_z2; // 华南区
  const bucketManager = new qiniu.rs.BucketManager(mac, config);
  const key = `commodity/${url.replace(qiniuConfig.domain, "")}`;
  bucketManager.delete(qiniuConfig.bucket, key, (err, respBody, respInfo) => {
    if (err) {
      return serverErr(res);
    } else {
      res.status(200).send({ code: "200", message: "删除成功" });
    }
  });
};
//查询权限接口
exports.power = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then((decoded) => {
        const sql = `
SELECT 
    u.*, r.*, ro.*
FROM 
    user u
JOIN 
    role r ON u.permission = r.roleId AND u.permission IN (u.permission)
LEFT JOIN 
    router ro ON ro.routerId IN (
        SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(r.routerIds, ',', numbers.n), ',', -1)
        FROM (
            SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
        ) numbers
        WHERE CHAR_LENGTH(r.routerIds) - CHAR_LENGTH(REPLACE(r.routerIds, ',', '')) + 1 >= numbers.n
    )
    WHERE 
    u.permission = ${decoded.permission} AND u.id = ${decoded.id}
		`;
        pool.query(sql, (err, result) => {
          if (err) {
            return serverErr(res);
          } else {
            let arr = [];
            let arrList = [];
            // 先处理父节点
            result.forEach((element) => {
              if (element.parentId && element.parentId !== null) {
                element.children = [];
                arr.push(element);
              }
            });
            // 再处理子节点
            result.forEach((element) => {
              if (!element.parentId || element.parentId === null) {
                arr.forEach((parent) => {
                  if (element.childrenId === parent.parentId) {
                    if (!parent.children) parent.children = [];
                    parent.children.push(element);
                  }
                });
              }
            });
            arrList = arr;
            res
              .status(200)
              .send({ code: "200", message: "查询成功", data: arrList });
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  }
};
//查询roleName接口
exports.roleName = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then((decoded) => {
        const sql = `SELECT * FROM role WHERE roleId=${decoded.permission}`;
        pool.query(sql, (err, result) => {
          if (err) {
            return serverErr(res);
          } else {
            res
              .status(200)
              .send({ code: "200", message: "查询成功", data: result[0] });
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  }
};
//修改密码接口
exports.changePassword = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then((decoded) => {
        const { passWord } = req.body;
        const sql = `UPDATE user SET passWord = ? WHERE id = ?`;
        pool.query(sql, [passWord, decoded.id], (err) => {
          if (err) {
            return serverErr(res);
          } else {
            res.status(200).send({ code: "200", message: "修改成功" });
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  }
};
//修改昵称接口
exports.changeName = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then((decoded) => {
        const { name } = req.body;
        const sql = `UPDATE user SET name = ? WHERE id = ?`;
        pool.query(sql, [name, decoded.id], (err) => {
          if (err) {
            return serverErr(res);
          } else {
            res.status(200).send({ code: "200", message: "修改成功" });
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  }
};
//查询角色接口
exports.role = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then(() => {
        const { roleName, page, pageSize } = req.body;
        let offset = (page - 1) * pageSize;
        let sql = ``;
        if (roleName) {
          sql += `SELECT * FROM role WHERE roleName=? LIMIT ${pageSize} OFFSET ${offset}`;
        } else {
          sql = `SELECT * FROM role LIMIT ${pageSize} OFFSET ${offset}`;
        }
        pool.query(sql, [roleName], (err, result) => {
          if (err) {
            return serverErr(res);
          } else {
            if (!result.length) {
              res
                .status(200)
                .send({ code: "200", message: "查询成功", data: [], total: 0 });
            } else {
              let total = 0;
              sql = `SELECT COUNT(*) AS total FROM role`;
              pool.query(sql, (err, count) => {
                if (err) {
                  return serverErr(res);
                } else {
                  total = count[0].total;
                  res.status(200).send({
                    code: "200",
                    message: "查询成功",
                    data: result,
                    total,
                  });
                }
              });
            }
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  }
};
//查询所有路由表接口
exports.router = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then(() => {
        let sql = `SELECT * FROM router`;
        pool.query(sql, (err, result) => {
          if (err) {
            return serverErr(res);
          } else {
            let arr = [];
            let arrList = [];
            result.forEach((element) => {
              if (element.parentId) {
                element.children = [];
                arr.push(element);
              } else {
                arr.forEach((i) => {
                  if (element.childrenId == i.parentId) {
                    i.children.push(element);
                  }
                });
              }
            });
            arrList = arr;
            res
              .status(200)
              .send({ code: "200", message: "查询成功", data: arrList });
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  }
};
//新增角色接口
exports.addRole = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then((decoded) => {
        const { roleName, showTheme, routerIds } = req.body;
        const founder = decoded.accountNumber;
        let sql = `INSERT INTO role (roleName,showTheme,routerIds,creationDate,founder) VALUES (?,?,?,?,?)`;
        pool.query(
          sql,
          [roleName, showTheme, routerIds, getNowData(), founder],
          (err, result) => {
            if (err) {
              return serverErr(res);
            } else {
              res.status(200).send({ code: "200", message: "新增成功" });
            }
          }
        );
      })
      .catch(() => {
        return tokenErr(res);
      });
  }
};
//删除角色接口
exports.deleteRole = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then(() => {
        const { roleId } = req.body;
        if (roleId == 1 || roleId == 2) {
          return res
            .status(200)
            .send({ code: "200", message: "管理员角色不能删除!" });
        }
        let sql = `DELETE FROM role WHERE roleId=?`;
        pool.query(sql, [roleId], (err) => {
          if (err) {
            return serverErr(res);
          } else {
            res.status(200).send({ code: "200", message: "删除成功" });
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  }
};
//修改角色接口
exports.changeRole = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then(() => {
        const { roleId, roleName, showTheme, routerIds } = req.body;
        if (roleId == 1 || roleId == 2) {
          return res
            .status(200)
            .send({ code: "200", message: "管理员角色不能编辑!" });
        }
        let sql = `UPDATE role SET roleName=?,showTheme=?,routerIds=? WHERE roleId=?`;
        pool.query(sql, [roleName, showTheme, routerIds, roleId], (err) => {
          if (err) {
            return serverErr(res);
          } else {
            res.status(200).send({ code: "200", message: "修改成功" });
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  }
};
//查询用户接口
exports.user = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then(() => {
        const { name, page, pageSize } = req.body;
        let offset = (page - 1) * pageSize;
        let sql = ``;
        if (name) {
          sql += `SELECT * FROM user WHERE name=? LIMIT ${pageSize} OFFSET ${offset}`;
        } else {
          sql = `SELECT * FROM user LIMIT ${pageSize} OFFSET ${offset}`;
        }
        pool.query(sql, [name], (err, result) => {
          if (err) {
            return serverErr(res);
          } else {
            if (!result.length) {
              res
                .status(200)
                .send({ code: "200", message: "查询成功", data: [], total: 0 });
            } else {
              let total = 0;
              sql = `SELECT COUNT(*) AS total FROM user`;
              pool.query(sql, (err, count) => {
                if (err) {
                  return serverErr(res);
                } else {
                  total = count[0].total;
                  result.forEach((element) => {
                    if (element.id == 1) {
                      result.splice(result.indexOf(element), 1);
                    }
                  });
                  res.status(200).send({
                    code: "200",
                    message: "查询成功",
                    data: result,
                    total,
                  });
                }
              });
            }
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  }
};
//查询所有角色
exports.allRole = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then(() => {
        let sql = `SELECT * FROM role`;
        pool.query(sql, (err, result) => {
          if (err) {
            return serverErr(res);
          } else {
            res
              .status(200)
              .send({ code: "200", message: "查询成功", data: result });
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  }
};
//新增用户
exports.addUser = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      const { accountNumber, passWord, permission, amount, name } = req.body;
      const sql = `SELECT * FROM user WHERE accountNumber = ?`;
      pool.query(sql, accountNumber, (err, data) => {
        if (err) {
          return serverErr(res);
        } else if (data.length !== 0) {
          res.status(200).send({ code: "400", message: "已有账号" });
        } else {
          const founder = decoded.accountNumber;
          const sql = `INSERT INTO user (accountNumber,passWord,permission,name,amount,creationDate,founder) VALUES (?,?,?,?,?,?,?)`;
          pool.query(
            sql,
            [
              accountNumber,
              passWord,
              permission,
              name,
              amount,
              getNowData(),
              founder,
            ],
            (err) => {
              if (err) {
                return serverErr(res);
              } else {
                const sql = `CREATE TABLE ${accountNumber}(
                  id int(11) NOT NULL AUTO_INCREMENT,
                  type int(11) DEFAULT NULL,
                  timer DATE DEFAULT NULL,
                  category int(11) DEFAULT NULL,
                  money double DEFAULT NULL,
                  card varchar(255) DEFAULT NULL,
                  notes varchar(255) DEFAULT NULL,
                  amount double DEFAULT NULL,
                  PRIMARY KEY (id)
                ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;`;
                pool.query(sql, (err) => {
                  if (err) {
                    return serverErr(res);
                  } else {
                    res
                      .status(200)
                      .send({ code: "200", message: "新增用户成功" });
                  }
                });
              }
            }
          );
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//编辑用户
exports.changeUser = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { id, accountNumber, passWord, permission, amount, name } =
        req.body;
      if (id == 1) {
        return res.status(200).send({ code: "200", message: "admin不能编辑!" });
      }
      const sql = `UPDATE user SET accountNumber=?,passWord=?,permission=?,name=?,amount=? WHERE id=?`;
      pool.query(
        sql,
        [accountNumber, passWord, permission, name, amount, id],
        (err, data) => {
          if (err) {
            return serverErr(res);
          } else {
            res.status(200).send({ code: "200", message: "修改成功" });
          }
        }
      );
    });
  } else {
    return tokenErr(res);
  }
};
//删除用户
exports.deleteUser = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { id, accountNumber } = req.body;
      if (id == 1) {
        return res.status(200).send({ code: "200", message: "admin不能删除!" });
      }
      const sql = `DELETE FROM user WHERE id=?`;
      pool.query(sql, id, (err) => {
        if (err) {
          return serverErr(res);
        } else {
          const sql = `DROP TABLE ${accountNumber}`;
          pool.query(sql, (err) => {
            if (err) {
              return serverErr(res);
            } else {
              res.status(200).send({ code: "200", message: "删除用户成功" });
            }
          });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//查询支出类目
exports.expenditureCategory = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { categoryName, page, pageSize } = req.body;
      let offset = (page - 1) * pageSize;
      let sql = ``;
      if (categoryName) {
        sql += `SELECT * FROM expenditurecategory WHERE categoryName=? LIMIT ${pageSize} OFFSET ${offset}`;
      } else {
        sql = `SELECT * FROM expenditurecategory LIMIT ${pageSize} OFFSET ${offset}`;
      }
      pool.query(sql, [categoryName], (err, result) => {
        if (err) {
          return serverErr(res);
        } else {
          if (!result.length) {
            res
              .status(200)
              .send({ code: "200", message: "查询成功", data: [], total: 0 });
          } else {
            let total = 0;
            sql = `SELECT COUNT(*) AS total FROM expenditurecategory`;
            pool.query(sql, (err, count) => {
              if (err) {
                return serverErr(res);
              } else {
                total = count[0].total;
                res.status(200).send({
                  code: "200",
                  message: "查询成功",
                  data: result,
                  total,
                });
              }
            });
          }
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//新增支出类目
exports.addExpenditureCategory = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      const { categoryNumber, categoryName } = req.body;
      const sql = `INSERT INTO expenditurecategory (categoryNumber,categoryName,creationDate,founder) VALUES (?,?,?,?)`;
      const founder = decoded.accountNumber;
      pool.query(
        sql,
        [categoryNumber, categoryName, getNowData(), founder],
        (err) => {
          if (err) {
            return serverErr(res);
          } else {
            res.status(200).send({ code: "200", message: "新增成功" });
          }
        }
      );
    });
  } else {
    return tokenErr(res);
  }
};
//修改支出类目
exports.changeExpenditureCategory = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { id, categoryNumber, categoryName } = req.body;
      const sql = `UPDATE expenditurecategory SET categoryNumber=?, categoryName=? WHERE id=?`;
      pool.query(sql, [categoryNumber, categoryName, id], (err) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "修改成功" });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//删除支出类目
exports.deleteExpenditureCategory = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { id } = req.body;
      const sql = `DELETE FROM expenditurecategory WHERE id=?`;
      pool.query(sql, id, (err) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "删除成功" });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//查询收入类目
exports.incomeCategory = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { categoryName, page, pageSize } = req.body;
      let offset = (page - 1) * pageSize;
      let sql = ``;
      if (categoryName) {
        sql += `SELECT * FROM incomecategory WHERE categoryName=? LIMIT ${pageSize} OFFSET ${offset}`;
      } else {
        sql = `SELECT * FROM incomecategory LIMIT ${pageSize} OFFSET ${offset}`;
      }
      pool.query(sql, [categoryName], (err, result) => {
        if (err) {
          return serverErr(res);
        } else {
          if (!result.length) {
            res
              .status(200)
              .send({ code: "200", message: "查询成功", data: [], total: 0 });
          } else {
            let total = 0;
            sql = `SELECT COUNT(*) AS total FROM incomecategory`;
            pool.query(sql, (err, count) => {
              if (err) {
                return serverErr(res);
              } else {
                total = count[0].total;
                res.status(200).send({
                  code: "200",
                  message: "查询成功",
                  data: result,
                  total,
                });
              }
            });
          }
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//新增收入类目
exports.addIncomeCategory = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      const { categoryNumber, categoryName } = req.body;
      const sql = `INSERT INTO incomecategory (categoryNumber,categoryName,creationDate,founder) VALUES (?,?,?,?)`;
      const founder = decoded.accountNumber;
      pool.query(
        sql,
        [categoryNumber, categoryName, getNowData(), founder],
        (err) => {
          if (err) {
            return serverErr(res);
          } else {
            res.status(200).send({ code: "200", message: "新增成功" });
          }
        }
      );
    });
  } else {
    return tokenErr(res);
  }
};
//修改收入类目
exports.changeIncomeCategory = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { id, categoryNumber, categoryName } = req.body;
      const sql = `UPDATE incomecategory SET categoryNumber=?, categoryName=? WHERE id=?`;
      pool.query(sql, [categoryNumber, categoryName, id], (err) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "修改成功" });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//删除收入类目
exports.deleteIncomeCategory = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { id } = req.body;
      const sql = `DELETE FROM incomecategory WHERE id=?`;
      pool.query(sql, id, (err) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "删除成功" });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//查询当前用户余额和卡号信息
exports.card = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      const { cardNumber, balance, page, pageSize } = req.body;
      let offset = (page - 1) * pageSize;
      let sql = ``;
      if (cardNumber) {
        sql = `SELECT card.* FROM card JOIN user ON card.userId = user.id WHERE user.id=? AND card.cardNumber=? LIMIT ${pageSize} OFFSET ${offset}`;
        pool.query(sql, [decoded.id, cardNumber], (err, data) => {
          if (err) {
            return serverErr(res);
          } else {
            let total = 0;
            sql = `SELECT COUNT(*) AS total FROM card`;
            pool.query(sql, (err, count) => {
              if (err) {
                return serverErr(res);
              } else {
                total = count[0].total;
                res
                  .status(200)
                  .send({ code: "200", message: "查询成功", data, total });
              }
            });
          }
        });
      } else if (balance) {
        sql = `SELECT card.* FROM card JOIN user ON card.userId = user.id WHERE user.id=? AND card.balance=? LIMIT ${pageSize} OFFSET ${offset}`;
        pool.query(sql, [decoded.id, balance], (err, data) => {
          if (err) {
            return serverErr(res);
          } else {
            let total = 0;
            sql = `SELECT COUNT(*) AS total FROM card`;
            pool.query(sql, (err, count) => {
              if (err) {
                return serverErr(res);
              } else {
                total = count[0].total;
                res
                  .status(200)
                  .send({ code: "200", message: "查询成功", data, total });
              }
            });
          }
        });
      } else {
        sql = `SELECT card.* FROM card JOIN user ON card.userId = user.id WHERE user.id=? LIMIT ${pageSize} OFFSET ${offset}`;
        pool.query(sql, decoded.id, (err, data) => {
          if (err) {
            return serverErr(res);
          } else {
            let total = 0;
            sql = `SELECT COUNT(*) AS total FROM card`;
            pool.query(sql, (err, count) => {
              if (err) {
                return serverErr(res);
              } else {
                total = count[0].total;
                res
                  .status(200)
                  .send({ code: "200", message: "查询成功", data, total });
              }
            });
          }
        });
      }
    });
  } else {
    return tokenErr(res);
  }
};
//新增卡号
exports.addCardNumber = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      const sql = `INSERT INTO card (userId,cardNumber,balance,creationDate) VALUES (?,?,0,?)`;
      const { cardNumber } = req.body;
      const userId = decoded.id;
      pool.query(sql, [userId, cardNumber, getNowData()], (err) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "新增成功" });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//修改卡号
exports.changeCardNumber = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      const sql = `UPDATE card SET cardNumber=? WHERE id=?`;
      const { cardNumber, id } = req.body;
      pool.query(sql, [cardNumber, id], (err, data) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "修改卡号成功" });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//删除卡号
exports.deleteCardNumber = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const sql = `DELETE FROM card WHERE id=?`;
      const { id } = req.body;
      pool.query(sql, id, (err, data) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "删除卡号成功" });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//修改余额
exports.changeMoney = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { balance, id } = req.body;
      const sql = `UPDATE card SET balance = ? WHERE id = ?`;
      pool.query(sql, [balance, id], (err) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "修改余额成功" });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//查询所有支出类目
exports.allExpenditureCategory = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const sql = `SELECT * FROM expenditurecategory`;
      pool.query(sql, (err, data) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "查询成功", data });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//查询支出
exports.expenditure = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      let nowDate = new Date();
      let year = nowDate.getFullYear();
      let month = nowDate.getMonth() + 1;
      let day = nowDate.getDate();
      let nowTime = `${year}-${month}-${day}`;
      const { startTime, endTime, money, category } = req.body;
      let sql = `SELECT * FROM ${decoded.accountNumber} WHERE type=0`;
      if (!startTime && !endTime && !category && !money) {
        sql += ` AND timer BETWEEN '${nowTime}' AND '${nowTime}'`;
      }
      if (startTime && endTime && category && money) {
        sql += ` AND timer BETWEEN '${startTime}' AND '${endTime}'`;
        sql += ` AND money = ${money}`;
        sql += ` AND category = ${category} `;
      }
      if (startTime && endTime && !category && !money) {
        sql += ` AND timer BETWEEN '${startTime}' AND '${endTime}'`;
      }
      if (startTime && endTime && category && !money) {
        sql += ` AND timer BETWEEN '${startTime}' AND '${endTime}'`;
        sql += ` AND category = ${category} `;
      }
      if (startTime && endTime && !category && money) {
        sql += ` AND timer BETWEEN '${startTime}' AND '${endTime}'`;
        sql += ` AND money = ${money}`;
      }

      if (!startTime && !endTime && category && !money) {
        sql += ` AND timer BETWEEN '${nowTime}' AND '${nowTime}'`;
        sql += ` AND category = ${category}`;
      }
      if (!startTime && !endTime && !category && money) {
        sql += ` AND timer BETWEEN '${nowTime}' AND '${nowTime}'`;
        sql += ` AND money = ${money} `;
      }
      if (!startTime && !endTime && category && money) {
        sql += ` AND timer BETWEEN '${nowTime}' AND '${nowTime}'`;
        sql += ` AND money = ${money}`;
        sql += ` AND category = ${category}`;
      }

      pool.query(sql, (err, data) => {
        if (err) {
          return serverErr(res);
        } else {
          let moneyTotal = new Decimal(0);
          data.forEach((item) => {
            const money = new Decimal(item.money);
            moneyTotal = moneyTotal.add(money);
          });
          let total = 0;
          total = data.length;
          res.status(200).send({ code: "200", data, moneyTotal, total });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//查询当前用户所有卡号
exports.allCardNumber = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      const sql = `SELECT * FROM card WHERE userId=${decoded.id}`;
      pool.query(sql, (err, data) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "查询成功", data });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//删除当前用户支出
exports.deleteExpenditure = async (req, res) => {
  const token = req.headers.token;
  if (!token) return tokenErr(res);

  try {
    const decoded = await verifyToken(token);
    const { id, money, card } = req.body;

    // 参数验证
    if (!id || !money || !card) {
      return res.status(400).send({ code: "400", message: "参数不完整" });
    }

    // 开始事务
    await new Promise((resolve, reject) => {
      pool.beginTransaction((err) => {
        if (err) reject(err);
        resolve();
      });
    });

    try {
      // 查询卡号余额
      const [cardData] = await new Promise((resolve, reject) => {
        pool.query(
          "SELECT balance FROM card WHERE userId = ? AND cardNumber = ?",
          [decoded.id, card],
          (err, result) => {
            if (err) reject(err);
            resolve(result);
          }
        );
      });

      // 计算新余额
      const currentBalance = new Decimal(cardData.balance);
      const changeAmount = new Decimal(money);
      const newBalance = currentBalance.add(changeAmount);

      // 更新卡号余额
      await new Promise((resolve, reject) => {
        pool.query(
          "UPDATE card SET balance = ? WHERE cardNumber = ? AND userId = ?",
          [newBalance.toString(), card, decoded.id],
          (err) => {
            if (err) reject(err);
            resolve();
          }
        );
      });

      // 删除支出记录
      await new Promise((resolve, reject) => {
        pool.query(
          `DELETE FROM ${decoded.accountNumber} WHERE id = ?`,
          [id],
          (err) => {
            if (err) reject(err);
            resolve();
          }
        );
      });

      // 提交事务
      await new Promise((resolve, reject) => {
        pool.commit((err) => {
          if (err) reject(err);
          resolve();
        });
      });

      res.status(200).send({ code: "200", message: "删除成功" });
    } catch (error) {
      // 回滚事务
      await new Promise((resolve) => {
        pool.rollback(() => resolve());
      });
      throw error;
    }
  } catch (error) {
    console.error("删除支出记录失败:", error);
    return serverErr(res);
  }
};
//新增支出
exports.addExpenditure = async (req, res) => {
  const token = req.headers.token;
  if (!token) return tokenErr(res);

  try {
    const decoded = await verifyToken(token);
    const { type, money, category, card, timer, notes } = req.body;

    // 开始事务
    await new Promise((resolve, reject) => {
      pool.beginTransaction((err) => (err ? reject(err) : resolve()));
    });

    try {
      // 查询卡号余额
      const [cardData] = await new Promise((resolve, reject) => {
        pool.query(
          "SELECT balance FROM card WHERE userId = ? AND cardNumber = ?",
          [decoded.id, card],
          (err, data) => (err ? reject(err) : resolve(data))
        );
      });

      // 计算新余额
      const currentBalance = new Decimal(cardData.balance);
      const expenseAmount = new Decimal(money);
      const newBalance = currentBalance.sub(expenseAmount);

      // 更新卡号余额
      await new Promise((resolve, reject) => {
        pool.query(
          "UPDATE card SET balance = ? WHERE cardNumber = ? AND userId = ?",
          [newBalance.toString(), card, decoded.id],
          (err) => (err ? reject(err) : resolve())
        );
      });

      // 插入支出记录
      await new Promise((resolve, reject) => {
        pool.query(
          `INSERT INTO ${decoded.accountNumber} (type, money, category, card, timer, notes) VALUES (?, ?, ?, ?, ?, ?)`,
          [type, money, category, card, timer, notes],
          (err) => (err ? reject(err) : resolve())
        );
      });

      // 提交事务
      await new Promise((resolve, reject) => {
        pool.commit((err) => (err ? reject(err) : resolve()));
      });

      res.status(200).send({ code: "200", message: "新增成功" });
    } catch (error) {
      // 回滚事务
      await new Promise((resolve) => pool.rollback(() => resolve()));
      throw error;
    }
  } catch (error) {
    console.error("添加支出失败:", error);
    return serverErr(res);
  }
};
//修改支出
exports.changeExpenditure = async (req, res) => {
  const token = req.headers.token;
  if (!token) return tokenErr(res);

  try {
    const decoded = await verifyToken(token);
    const { id, type, money, category, card, timer, notes, oldMoney } =
      req.body;

    // 开始事务
    await new Promise((resolve, reject) => {
      pool.beginTransaction((err) => (err ? reject(err) : resolve()));
    });

    try {
      // 查询当前用户卡号余额
      const [cardData] = await new Promise((resolve, reject) => {
        pool.query(
          "SELECT * FROM card WHERE userId = ? AND cardNumber = ?",
          [decoded.id, card],
          (err, result) => (err ? reject(err) : resolve(result))
        );
      });

      // 计算新余额
      const balance = new Decimal(cardData.balance);
      const oldAmount = new Decimal(oldMoney);
      const newAmount = new Decimal(money);
      const newBalance = balance.add(oldAmount).sub(newAmount);

      // 更新卡号余额
      await new Promise((resolve, reject) => {
        pool.query(
          "UPDATE card SET balance = ? WHERE cardNumber = ? AND userId = ?",
          [newBalance.toString(), card, decoded.id],
          (err) => (err ? reject(err) : resolve())
        );
      });

      // 更新支出记录
      await new Promise((resolve, reject) => {
        pool.query(
          `UPDATE ${decoded.accountNumber} SET type = ?, money = ?, category = ?, card = ?, timer = ?, notes = ? WHERE id = ?`,
          [type, money, category, card, timer, notes, id],
          (err) => (err ? reject(err) : resolve())
        );
      });

      // 提交事务
      await new Promise((resolve, reject) => {
        pool.commit((err) => (err ? reject(err) : resolve()));
      });

      res.status(200).send({ code: "200", message: "修改成功" });
    } catch (error) {
      // 回滚事务
      await new Promise((resolve) => pool.rollback(resolve));
      throw error;
    }
  } catch (error) {
    console.error("修改支出失败:", error);
    return serverErr(res);
  }
};

//查询所有收入类目
exports.allIncomeCategory = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const sql = `SELECT * FROM incomecategory`;
      pool.query(sql, (err, data) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "查询成功", data });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//查询收入
exports.income = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      let nowDate = new Date();
      let year = nowDate.getFullYear();
      let month = nowDate.getMonth() + 1;
      let day = nowDate.getDate();
      let nowTime = `${year}-${month}-${day}`;
      const { startTime, endTime, money, category } = req.body;
      let sql = `SELECT * FROM ${decoded.accountNumber} WHERE type=1`;
      if (!startTime && !endTime && !category && !money) {
        sql += ` AND timer BETWEEN '${nowTime}' AND '${nowTime}'`;
      }
      if (startTime && endTime && category && money) {
        sql += ` AND timer BETWEEN '${startTime}' AND '${endTime}'`;
        sql += ` AND money = ${money}`;
        sql += ` AND category = ${category} `;
      }
      if (startTime && endTime && !category && !money) {
        sql += ` AND timer BETWEEN '${startTime}' AND '${endTime}'`;
      }
      if (startTime && endTime && category && !money) {
        sql += ` AND timer BETWEEN '${startTime}' AND '${endTime}'`;
        sql += ` AND category = ${category} `;
      }
      if (startTime && endTime && !category && money) {
        sql += ` AND timer BETWEEN '${startTime}' AND '${endTime}'`;
        sql += ` AND money = ${money}`;
      }

      if (!startTime && !endTime && category && !money) {
        sql += ` AND timer BETWEEN '${nowTime}' AND '${nowTime}'`;
        sql += ` AND category = ${category}`;
      }
      if (!startTime && !endTime && !category && money) {
        sql += ` AND timer BETWEEN '${nowTime}' AND '${nowTime}'`;
        sql += ` AND money = ${money} `;
      }
      if (!startTime && !endTime && category && money) {
        sql += ` AND timer BETWEEN '${nowTime}' AND '${nowTime}'`;
        sql += ` AND money = ${money}`;
        sql += ` AND category = ${category}`;
      }

      pool.query(sql, (err, data) => {
        if (err) {
          return serverErr(res);
        } else {
          let moneyTotal = new Decimal(0);
          data.forEach((item) => {
            const money = new Decimal(item.money);
            moneyTotal = moneyTotal.add(money);
          });
          let total = 0;
          total = data.length;
          res.status(200).send({ code: "200", data, moneyTotal, total });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//删除当前用户收入
exports.deleteIncome = (req, res) => {
  const token = req.headers.token;
  if (!token) return tokenErr(res);

  verifyToken(token)
    .then((decoded) => {
      const { id, money, card } = req.body;
      if (!id || !money || !card) {
        return res.status(400).send({ code: "400", message: "参数不完整" });
      }

      pool.beginTransaction((err) => {
        if (err) return serverErr(res);

        const sql = `
          UPDATE card c
          JOIN (SELECT balance FROM card WHERE userId = ? AND cardNumber = ? FOR UPDATE) b
          SET c.balance = c.balance - ?
          WHERE c.userId = ? AND c.cardNumber = ?;
          DELETE FROM ?? WHERE id = ?;
        `;

        pool.query(
          sql,
          [
            decoded.id,
            card,
            money,
            decoded.id,
            card,
            decoded.accountNumber,
            id,
          ],
          (err, result) => {
            if (err) {
              return pool.rollback(() => serverErr(res));
            }

            if (result[0].affectedRows === 0) {
              return pool.rollback(() => {
                res
                  .status(400)
                  .send({ code: "400", message: "卡号不存在或余额不足" });
              });
            }

            pool.commit((err) => {
              if (err) {
                return pool.rollback(() => serverErr(res));
              }
              res.status(200).send({ code: "200", message: "删除成功" });
            });
          }
        );
      });
    })
    .catch(() => tokenErr(res));
};
//新增收入
exports.addIncome = (req, res) => {
  const token = req.headers.token;
  if (!token) return tokenErr(res);

  verifyToken(token)
    .then((decoded) => {
      const { type, money, category, card, timer, notes } = req.body;

      // 金额校验
      if (!money || isNaN(money)) {
        return res.status(200).send({ code: "400", message: "无效的金额" });
      }

      // 开始事务
      pool.beginTransaction((err) => {
        if (err) return serverErr(res);

        // 查询卡号余额并更新
        const queryCard = `SELECT balance FROM card WHERE userId = ? AND cardNumber = ?`;
        pool.query(queryCard, [decoded.id, card], (err, data) => {
          if (err) {
            return pool.rollback(() => serverErr(res));
          }
          if (!data.length) {
            return pool.rollback(() => {
              res.status(200).send({ code: "400", message: "卡号不存在" });
            });
          }

          const currentBalance = new Decimal(data[0].balance);
          const changeAmount = new Decimal(money);
          const newBalance = currentBalance.add(changeAmount);

          // 更新卡号余额
          const updateCard = `UPDATE card SET balance = ? WHERE cardNumber = ? AND userId = ?`;
          pool.query(
            updateCard,
            [newBalance.toString(), card, decoded.id],
            (err) => {
              if (err) {
                return pool.rollback(() => serverErr(res));
              }

              // 记录交易
              const insertTransaction = `INSERT INTO ${decoded.accountNumber} (type, money, category, card, timer, notes) VALUES (?, ?, ?, ?, ?, ?)`;
              pool.query(
                insertTransaction,
                [type, money, category, card, timer, notes],
                (err) => {
                  if (err) {
                    return pool.rollback(() => serverErr(res));
                  }

                  // 提交事务
                  pool.commit((err) => {
                    if (err) {
                      return pool.rollback(() => serverErr(res));
                    }
                    res.status(200).send({ code: "200", message: "新增成功" });
                  });
                }
              );
            }
          );
        });
      });
    })
    .catch(() => tokenErr(res));
};
//修改收入
exports.changeIncome = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then((decoded) => {
        const { id, type, money, category, card, timer, notes, oldMoney } =
          req.body;

        // 参数校验
        if (!id || !type || !money || !category || !card || !timer) {
          return res.status(200).send({ code: "400", message: "参数不完整" });
        }

        // 开始事务
        pool.beginTransaction(async (err) => {
          if (err) return serverErr(res);

          try {
            // 查询卡号余额并锁定行
            const [cardData] = await new Promise((resolve, reject) => {
              pool.query(
                "SELECT * FROM card WHERE userId = ? AND cardNumber = ? FOR UPDATE",
                [decoded.id, card],
                (err, result) => (err ? reject(err) : resolve(result))
              );
            });

            // 计算新余额
            const currentBalance = new Decimal(cardData.balance);
            const oldAmount = new Decimal(oldMoney || 0);
            const newAmount = new Decimal(money);
            const newBalance = currentBalance.sub(oldAmount).add(newAmount);

            // 更新卡号余额
            await new Promise((resolve, reject) => {
              pool.query(
                "UPDATE card SET balance = ? WHERE cardNumber = ? AND userId = ?",
                [newBalance.toString(), card, decoded.id],
                (err) => (err ? reject(err) : resolve())
              );
            });

            // 更新交易记录
            await new Promise((resolve, reject) => {
              pool.query(
                `UPDATE ${decoded.accountNumber} SET type = ?, money = ?, category = ?, card = ?, timer = ?, notes = ? WHERE id = ?`,
                [type, money, category, card, timer, notes, id],
                (err) => (err ? reject(err) : resolve())
              );
            });

            // 提交事务
            pool.commit((err) => {
              if (err) {
                return pool.rollback(() => serverErr(res));
              }
              res.status(200).send({ code: "200", message: "修改成功" });
            });
          } catch (error) {
            return pool.rollback(() => serverErr(res));
          }
        });
      })
      .catch(() => tokenErr(res));
  }
};
//查询总收入
exports.incomeSummary = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      let sql = `SELECT * FROM ${decoded.accountNumber} WHERE type=1`;
      pool.query(sql, (err, data) => {
        if (err) {
          return serverErr(res);
        } else {
          let moneyTotal = new Decimal(0);
          data.forEach((item) => {
            const money = new Decimal(item.money);
            moneyTotal = moneyTotal.add(money);
          });
          moneyTotal = moneyTotal.toNumber();
          res.status(200).send({ code: "200", moneyTotal });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//查询总支出
exports.expenditureSummary = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      let sql = `SELECT * FROM ${decoded.accountNumber} WHERE type=0`;
      pool.query(sql, (err, data) => {
        if (err) {
          serverErr(res);
        } else {
          //money总和
          let moneyTotal = new Decimal(0);
          data.forEach((item) => {
            const money = new Decimal(item.money);
            moneyTotal = moneyTotal.add(money);
          });
          moneyTotal = moneyTotal.toNumber();
          res.status(200).send({ code: "200", moneyTotal });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//查询本年收入
exports.incomeYear = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      let nowDate = new Date();
      let year = nowDate.getFullYear();
      let month = nowDate.getMonth() + 1;
      let dayOne = "01";
      let monthOne = "01";
      let day = nowDate.getDate();
      let startTime = `${year}-${monthOne}-${dayOne}`;
      let endTime = `${year}-${month}-${day}`;
      let sql = `SELECT * FROM ${decoded.accountNumber} WHERE type=1 AND timer BETWEEN '${startTime}' AND '${endTime}'`;
      pool.query(sql, (err, data) => {
        if (err) {
          return serverErr(res);
        } else {
          let moneyTotal = new Decimal(0);
          data.forEach((item) => {
            const money = new Decimal(item.money);
            moneyTotal = moneyTotal.add(money);
          });
          moneyTotal = moneyTotal.toNumber();
          res.status(200).send({ code: "200", moneyTotal });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//查询本年支出
exports.expenditureYear = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      let nowDate = new Date();
      let year = nowDate.getFullYear();
      let month = nowDate.getMonth() + 1;
      let dayOne = "01";
      let monthOne = "01";
      let day = nowDate.getDate();
      let startTime = `${year}-${monthOne}-${dayOne}`;
      let endTime = `${year}-${month}-${day}`;
      let sql = `SELECT * FROM ${decoded.accountNumber} WHERE type=0 AND timer BETWEEN '${startTime}' AND '${endTime}'`;
      pool.query(sql, (err, data) => {
        if (err) {
          return serverErr(res);
        } else {
          //money总和
          let moneyTotal = new Decimal(0);
          data.forEach((item) => {
            const money = new Decimal(item.money);
            moneyTotal = moneyTotal.add(money);
          });
          moneyTotal = moneyTotal.toNumber();
          res.status(200).send({ code: "200", moneyTotal });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//查询总余额
exports.totalBalance = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      let sql = `SELECT * FROM ${decoded.accountNumber} WHERE type=1`;
      pool.query(sql, (err, data) => {
        if (err) {
          return serverErr(res);
        } else {
          let income = new Decimal(0);
          data.forEach((item) => {
            const money = new Decimal(item.money);
            income = income.add(money);
          });
          income = income.toNumber();
          let sql = `SELECT * FROM ${decoded.accountNumber} WHERE type=0`;
          pool.query(sql, (err, data) => {
            if (err) {
              return serverErr(res);
            } else {
              let expenditure = new Decimal(0);
              data.forEach((item) => {
                const money = new Decimal(item.money);
                expenditure = expenditure.add(money);
              });
              expenditure = expenditure.toNumber();
              let moneyTotal = new Decimal(income).sub(expenditure).toNumber();
              res.status(200).send({ code: "200", moneyTotal });
            }
          });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//查询商品类目
exports.allCommodityCategory = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { categoryName, page, pageSize } = req.body;
      let offset = (page - 1) * pageSize;
      let sql = ``;
      if (categoryName) {
        sql += `SELECT * FROM commoditycategory WHERE categoryName=? LIMIT ${pageSize} OFFSET ${offset}`;
      } else {
        sql = `SELECT * FROM commoditycategory LIMIT ${pageSize} OFFSET ${offset}`;
      }
      pool.query(sql, [categoryName], (err, result) => {
        if (err) {
          return serverErr(res);
        } else {
          if (!result.length) {
            res
              .status(200)
              .send({ code: "200", message: "查询成功", data: [], total: 0 });
          } else {
            let total = 0;
            sql = `SELECT COUNT(*) AS total FROM commoditycategory`;
            pool.query(sql, (err, count) => {
              if (err) {
                return serverErr(res);
              } else {
                total = count[0].total;
                res.status(200).send({
                  code: "200",
                  message: "查询成功",
                  data: result,
                  total,
                });
              }
            });
          }
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//查询所有商品类目
exports.allCommodityCategoryAll = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const sql = `SELECT * FROM commoditycategory`;
      pool.query(sql, (err, data) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", data });
        }
      });
    });
  }
};
//删除商品类目
exports.deleteCommodityCategory = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { id } = req.body;
      const sql = `DELETE FROM commoditycategory WHERE id=?`;
      pool.query(sql, id, (err) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "删除成功" });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//新增商品类目
exports.addCommodityCategory = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      const { categoryNumber, categoryName } = req.body;
      const sql = `INSERT INTO commoditycategory (categoryNumber,categoryName,creationDate,founder) VALUES (?,?,?,?)`;
      const founder = decoded.accountNumber;
      pool.query(
        sql,
        [categoryNumber, categoryName, getNowData(), founder],
        (err) => {
          if (err) {
            return serverErr(res);
          } else {
            res.status(200).send({ code: "200", message: "新增成功" });
          }
        }
      );
    });
  } else {
    return tokenErr(res);
  }
};
//修改商品类目
exports.changeCommodityCategory = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { id, categoryNumber, categoryName } = req.body;
      const sql = `UPDATE commoditycategory SET categoryNumber=?,categoryName=? WHERE id=?`;
      pool.query(sql, [categoryNumber, categoryName, id], (err) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "修改成功" });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//查询卡密
exports.allCarmi = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { code, page, pageSize } = req.body;
      let offset = (page - 1) * pageSize;
      let sql = ``;
      if (code) {
        sql += `SELECT * FROM carmi WHERE code=? LIMIT ${pageSize} OFFSET ${offset}`;
      } else {
        sql = `SELECT * FROM carmi LIMIT ${pageSize} OFFSET ${offset}`;
      }
      pool.query(sql, [code], (err, result) => {
        if (err) {
          return serverErr(res);
        } else {
          if (!result.length) {
            res
              .status(200)
              .send({ code: "200", message: "查询成功", data: [], total: 0 });
          } else {
            let total = 0;
            sql = `SELECT COUNT(*) AS total FROM carmi`;
            pool.query(sql, (err, count) => {
              if (err) {
                return serverErr(res);
              } else {
                total = count[0].total;
                res.status(200).send({
                  code: "200",
                  message: "查询成功",
                  data: result,
                  total,
                });
              }
            });
          }
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//新增卡密
exports.addCarmi = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      const { id, code, amount } = req.body;
      const sql = `INSERT INTO carmi (id,code,amount,creationDate,founder) VALUES (?,?,?,?,?)`;
      const founder = decoded.accountNumber;
      pool.query(sql, [id, code, amount, getNowData(), founder], (err) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "新增成功" });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//修改卡密
exports.changeCarmi = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { id, code, amount } = req.body;
      const sql = `UPDATE carmi SET code=?,amount=? WHERE id=?`;
      pool.query(sql, [code, amount, id], (err) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "修改成功" });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//删除卡密
exports.deleteCarmi = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { id } = req.body;
      const sql = `DELETE FROM carmi WHERE id=?`;
      pool.query(sql, id, (err) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "删除成功" });
        }
      });
    });
  } else {
    return tokenErr(res);
  }
};
//使用卡密
exports.useCarmi = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then((decoded) => {
        const { code } = req.body;
        // 开始事务
        pool.beginTransaction((err) => {
          if (err) {
            return serverErr(res);
          }
          // 查询卡密是否存在且未使用
          const checkCarmiSql = `SELECT * FROM carmi WHERE code = ?`;
          pool.query(checkCarmiSql, [code], (err, carmiResult) => {
            if (err) {
              return pool.rollback(() => {
                return serverErr(res);
              });
            }
            if (carmiResult.length === 0) {
              return pool.rollback(() => {
                res
                  .status(400)
                  .send({ code: "200", message: "卡密不存在或已被使用" });
              });
            }
            const currentAmount = new Decimal(decoded.amount);
            const addAmount = new Decimal(carmiResult[0].amount);
            const newAmount = currentAmount.add(addAmount);
            // 更新用户余额
            const updateUserSql = `UPDATE user SET amount = ? WHERE id = ?`;
            pool.query(
              updateUserSql,
              [newAmount.toString(), decoded.id],
              (err) => {
                if (err) {
                  return pool.rollback(() => {
                    return serverErr(res);
                  });
                }
                // 删除卡密
                const updateCarmiSql = `DELETE FROM carmi WHERE code=?`;
                pool.query(updateCarmiSql, [code, decoded.id], (err) => {
                  if (err) {
                    return pool.rollback(() => {
                      return serverErr(res);
                    });
                  }
                  // 提交事务
                  pool.commit((err) => {
                    if (err) {
                      return pool.rollback(() => {
                        return serverErr(res);
                      });
                    }
                    res.status(200).send({
                      code: "200",
                      message: "卡密使用成功",
                      data: {
                        amount: carmiResult[0].amount,
                      },
                    });
                  });
                });
              }
            );
          });
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  }
};
//查询商品
exports.allCommodity = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then(() => {
        const {
          commodityName,
          commodityAmount,
          state,
          category,
          page,
          pageSize,
        } = req.body;
        let offset = (page - 1) * pageSize;
        let conditions = [];
        let params = [];

        if (commodityName) {
          conditions.push("commodityName = ?");
          params.push(commodityName);
        }
        if (commodityAmount) {
          conditions.push("commodityAmount = ?");
          params.push(commodityAmount);
        }
        if (state) {
          conditions.push("state = ?");
          params.push(state);
        }
        if (category) {
          conditions.push("category = ?");
          params.push(category);
        }

        let sql = "SELECT * FROM commodity";
        if (conditions.length > 0) {
          sql += " WHERE " + conditions.join(" AND ");
        }
        if (category != 0) {
          sql += ` LIMIT ${pageSize} OFFSET ${offset}`;
        }

        pool.query(sql, params, (err, result) => {
          if (err) {
            return serverErr(res);
          } else {
            if (!result.length) {
              res
                .status(200)
                .send({ code: "200", message: "查询成功", data: [], total: 0 });
            } else {
              let countSql = "SELECT COUNT(*) AS total FROM commodity";
              if (conditions.length > 0) {
                countSql += " WHERE " + conditions.join(" AND ");
              }
              pool.query(countSql, params, (err, count) => {
                if (err) {
                  return serverErr(res);
                } else {
                  const total = count[0].total;
                  res.status(200).send({
                    code: "200",
                    message: "查询成功",
                    data: result,
                    total,
                  });
                }
              });
            }
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  } else {
    return tokenErr(res);
  }
};
//随机返回3个商品
exports.randomCommodity = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then(() => {
        const sql = `SELECT * FROM commodity WHERE state=0 ORDER BY RAND() LIMIT 3`;
        pool.query(sql, (err, result) => {
          if (err) {
            return serverErr(res);
          } else {
            res.status(200).send({
              code: "200",
              message: "查询成功",
              data: result,
            });
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  } else {
    return tokenErr(res);
  }
};
//查询salesVolume前四的商品
exports.salesVolumeFour = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const sql = `SELECT * FROM commodity ORDER BY salesVolume DESC LIMIT 4`;
      pool.query(sql, (err, result) => {
        if (err) {
          return serverErr(res);
        } else {
          res
            .status(200)
            .send({ code: "200", message: "查询成功", data: result });
        }
      });
    });
  }
};
//删除商品
exports.deleteCommodity = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { id } = req.body;
      const sql = `DELETE FROM commodity WHERE id=?`;
      pool.query(sql, id, (err) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "删除成功" });
        }
      });
    });
  }
};
//新增商品
exports.addCommodity = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const {
        id,
        image,
        commodityName,
        category,
        state,
        salesVolume,
        commodityAmount,
        star,
      } = req.body;
      const sql = `INSERT INTO commodity (id,image,commodityName,category,state,salesVolume,commodityAmount,star) VALUES (?,?,?,?,?,?,?,?)`;
      pool.query(
        sql,
        [
          id,
          image,
          commodityName,
          category,
          state,
          salesVolume,
          commodityAmount,
          star,
        ],
        (err) => {
          if (err) {
            return serverErr(res);
          } else {
            res.status(200).send({ code: "200", message: "新增成功" });
          }
        }
      );
    });
  }
};
//修改商品
exports.changeCommodity = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const {
        id,
        image,
        commodityName,
        category,
        state,
        salesVolume,
        commodityAmount,
        star,
      } = req.body;
      const sql = `UPDATE commodity SET image=?,commodityName=?,category=?,state=?,salesVolume=?,commodityAmount=?,star=? WHERE id=?`;
      pool.query(
        sql,
        [
          image,
          commodityName,
          category,
          state,
          salesVolume,
          commodityAmount,
          star,
          id,
        ],
        (err) => {
          if (err) {
            return serverErr(res);
          } else {
            res.status(200).send({ code: "200", message: "修改成功" });
          }
        }
      );
    });
  }
};
//查询订单
exports.allOrder = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then(() => {
        const { orderDate, deliveryDate, userId, state, page, pageSize } =
          req.body;
        let offset = (page - 1) * pageSize;
        let conditions = [];
        let params = [];

        if (orderDate) {
          conditions.push("orderDate = ?");
          params.push(orderDate);
        }
        if (deliveryDate) {
          conditions.push("deliveryDate = ?");
          params.push(deliveryDate);
        }
        if (userId) {
          conditions.push("userId = ?");
          params.push(userId);
        }
        if (state) {
          conditions.push("state = ?");
          params.push(state);
        }
        let sql = "SELECT * FROM orderlist";
        if (conditions.length > 0) {
          sql += " WHERE " + conditions.join(" AND ");
        }
        sql += ` LIMIT ${pageSize} OFFSET ${offset}`;

        pool.query(sql, params, (err, result) => {
          if (err) {
            return serverErr(res);
          } else {
            if (!result.length) {
              res
                .status(200)
                .send({ code: "200", message: "查询成功", data: [], total: 0 });
            } else {
              let countSql = "SELECT COUNT(*) AS total FROM orderlist";
              if (conditions.length > 0) {
                countSql += " WHERE " + conditions.join(" AND ");
              }
              pool.query(countSql, params, (err, count) => {
                if (err) {
                  return serverErr(res);
                } else {
                  const total = count[0].total;
                  res.status(200).send({
                    code: "200",
                    message: "查询成功",
                    data: result,
                    total,
                  });
                }
              });
            }
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  } else {
    return tokenErr(res);
  }
};
//查询所有用户
exports.allUser = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      let sql = "SELECT * FROM user";
      pool.query(sql, (err, result) => {
        if (err) {
          return serverErr(res);
        } else {
          if (!result.length) {
            res
              .status(200)
              .send({ code: "200", message: "查询成功", data: [], total: 0 });
          } else {
            res.status(200).send({
              code: "200",
              message: "查询成功",
              data: result.map((item) => ({ id: item.id, name: item.name })),
            });
          }
        }
      });
    });
  }
};
//删除订单
exports.deleteOrder = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then(() => {
      const { id } = req.body;
      const sql = `DELETE FROM orderlist WHERE id=?`;
      pool.query(sql, id, (err) => {
        if (err) {
          return serverErr(res);
        } else {
          res.status(200).send({ code: "200", message: "删除成功" });
        }
      });
    });
  }
};
//修改订单状态
exports.changeOrderState = async (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token).then((decoded) => {
      const { id, state } = req.body;
      if (state == 3) {
        // 开始事务
        pool.beginTransaction(async (err) => {
          if (err) return serverErr(res);

          try {
            // 开始事务
            await new Promise((resolve, reject) => {
              pool.beginTransaction((err) => {
                if (err) reject(err);
                resolve();
              });
            });

            // 查询订单信息
            const [orderInfo] = await new Promise((resolve, reject) => {
              pool.query(
                "SELECT * FROM orderlist WHERE id = ?",
                [id],
                (err, result) => {
                  if (err) reject(err);
                  resolve(result);
                }
              );
            });

            // 查询用户余额
            const [userInfo] = await new Promise((resolve, reject) => {
              pool.query(
                "SELECT amount FROM user WHERE id = ?",
                [orderInfo.userId],
                (err, result) => {
                  if (err) reject(err);
                  resolve(result);
                }
              );
            });

            // 计算新余额
            const useAmount = new Decimal(userInfo.amount);
            const orderAmount = new Decimal(orderInfo.totalAmount);
            const newAmount = useAmount.add(orderAmount);

            //更新用户余额
            await new Promise((resolve, reject) => {
              pool.query(
                "UPDATE user SET amount = ? WHERE id = ?",
                [newAmount.toString(), orderInfo.userId],
                (err) => {
                  if (err) reject(err);
                  resolve();
                }
              );
            });

            // 更新订单状态
            await new Promise((resolve, reject) => {
              pool.query(
                "UPDATE orderlist SET state = ? WHERE id = ?",
                [state, id],
                (err) => {
                  if (err) reject(err);
                  resolve();
                }
              );
            });

            // 提交事务
            pool.commit((err) => {
              if (err) {
                return pool.rollback(() => serverErr(res));
              }
              res.status(200).send({ code: "200", message: "退款成功" });
            });
          } catch (error) {
            return pool.rollback(() => serverErr(res));
          }
        });
      } else {
        const sql = `UPDATE orderlist SET state=? WHERE id=?`;
        pool.query(sql, [state, id], (err) => {
          if (err) {
            return serverErr(res);
          } else {
            res.status(200).send({ code: "200", message: "修改成功" });
          }
        });
      }
    });
  }
};
//商城商品
exports.shoppingCommodity = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then(() => {
        const { commodityName, commodityAmount, category, page, pageSize } =
          req.body;
        let offset = (page - 1) * pageSize;
        let conditions = ["state = 0"]; // 默认添加state=0条件
        let params = [];

        if (commodityName) {
          conditions.push("commodityName = ?");
          params.push(commodityName);
        }
        if (commodityAmount) {
          conditions.push("commodityAmount = ?");
          params.push(commodityAmount);
        }
        if (category) {
          conditions.push("category = ?");
          params.push(category);
        }

        let sql = "SELECT * FROM commodity WHERE " + conditions.join(" AND ");
        if (category != 0) {
          sql += ` LIMIT ${pageSize} OFFSET ${offset}`;
        }

        pool.query(sql, params, (err, result) => {
          if (err) {
            return serverErr(res);
          } else {
            if (!result.length) {
              res
                .status(200)
                .send({ code: "200", message: "查询成功", data: [], total: 0 });
            } else {
              let countSql =
                "SELECT COUNT(*) AS total FROM commodity WHERE " +
                conditions.join(" AND ");
              pool.query(countSql, params, (err, count) => {
                if (err) {
                  return serverErr(res);
                } else {
                  const total = count[0].total;
                  res.status(200).send({
                    code: "200",
                    message: "查询成功",
                    data: result,
                    total,
                  });
                }
              });
            }
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  } else {
    return tokenErr(res);
  }
};
//查询当前用户订单
exports.userOrder = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then((decoded) => {
        const { state, page, pageSize } = req.body;
        let offset = (page - 1) * pageSize;
        let conditions = [`userId = ${decoded.id}`];
        let params = [];

        if (state) {
          conditions.push(`state = ${state}`);
        }

        let sql = `SELECT * FROM orderlist WHERE ${conditions.join(
          " AND "
        )} LIMIT ${pageSize} OFFSET ${offset}`;

        pool.query(sql, params, (err, result) => {
          if (err) {
            return serverErr(res);
          } else {
            if (!result.length) {
              res
                .status(200)
                .send({ code: "200", message: "查询成功", data: [], total: 0 });
            } else {
              let countSql = "SELECT COUNT(*) AS total FROM orderlist";
              if (conditions.length > 0) {
                countSql += " WHERE " + conditions.join(" AND ");
              }
              pool.query(countSql, params, (err, count) => {
                if (err) {
                  return serverErr(res);
                } else {
                  const total = count[0].total;
                  res.status(200).send({
                    code: "200",
                    message: "查询成功",
                    data: result,
                    total,
                  });
                }
              });
            }
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  } else {
    return tokenErr(res);
  }
};
//付款
exports.paymentOrder = (req, res) => {
  const token = req.headers.token;
  if (token) {
    verifyToken(token)
      .then((decoded) => {
        const {
          id,
          orderContent,
          totalAmount,
          commodityIds,
          deliveryDate,
          remarks,
        } = req.body;
        //先commodityIds的商品销量加一
        pool.beginTransaction(async (err) => {
          if (err) return serverErr(res);

          try {
            // 首先判断用户余额是否小于totalAmount
            const [user] = await new Promise((resolve, reject) => {
              pool.query(
                "SELECT amount FROM user WHERE id = ? FOR UPDATE",
                [decoded.id],
                (err, result) => (err ? reject(err) : resolve(result))
              );
            });

            const userBalance = new Decimal(user.amount);
            const orderAmount = new Decimal(totalAmount);

            if (userBalance.lessThan(orderAmount)) {
              return pool.rollback(() => {
                res.status(200).send({ code: "400", message: "余额不足" });
              });
            }

            // 将逗号分隔的ID字符串转换为数组
            const ids = commodityIds.split(",").map((id) => id.trim());

            // 商品销量加一
            for (const commodityId of ids) {
              await new Promise((resolve, reject) => {
                pool.query(
                  "UPDATE commodity SET salesVolume = salesVolume + 1 WHERE id = ?",
                  [commodityId],
                  (err) => (err ? reject(err) : resolve())
                );
              });
            }
            const newAmount = userBalance.sub(orderAmount);
            // 扣除用户余额
            await new Promise((resolve, reject) => {
              pool.query(
                "UPDATE user SET amount = ? WHERE id = ?",
                [newAmount.toString(), decoded.id],
                (err) => (err ? reject(err) : resolve())
              );
            });
            function getOrderDate() {
              return dayjs().format("YYYY-MM-DD");
            }
            function getDeliveryDate() {
              return dayjs(deliveryDate).format("YYYY-MM-DD");
            }
            const sql = `INSERT INTO orderlist (id, userId, orderContent,commodityIds, totalAmount, orderDate, deliveryDate, remarks, state) 
                         VALUES (?,?,?,?,?,?,?,?,0)`;
            await new Promise((resolve, reject) => {
              pool.query(
                sql,
                [
                  id,
                  decoded.id,
                  orderContent,
                  commodityIds,
                  totalAmount,
                  getOrderDate(),
                  getDeliveryDate(),
                  remarks,
                ],
                (err) => (err ? reject(err) : resolve())
              );
            });
            // 提交事务
            pool.commit((err) => {
              if (err) {
                return pool.rollback(() => serverErr(res));
              }
              res.status(200).send({ code: "200", message: "付款成功" });
            });
          } catch (error) {
            return pool.rollback(() => serverErr(res));
          }
        });
      })
      .catch(() => {
        return tokenErr(res);
      });
  } else {
    return tokenErr(res);
  }
};
